package fina2.dcs.common.xls;

import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.TreeMap;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.jboss.seam.log.Log;
import org.jboss.seam.log.Logging;

import fina2.dcs.common.returns.jaxb.HEADERTYPE;
import fina2.dcs.common.returns.jaxb.ITEMTYPE;

@SuppressWarnings("unused")
public class XLSReader {
	private File matrix;
	private File xls;
	private InputStream matrixIn;
	private InputStream xlsIn;
	private POIFSFileSystem matrixPoi;
	private POIFSFileSystem xlsPoi;
	private HSSFWorkbook matrixWb;
	private HSSFWorkbook xlsWb;
	private Properties p = null;
	private Log log = Logging.getLog(getClass());
	private Map<String, List<OptionsRowInfo>> pointMap = null;
	private String correctPattern = null;
	private String type = null;

	/**
	 * Initializes matrix workbook and xls workbook
	 * 
	 * @param matrix
	 * @param xls
	 */
	public XLSReader(File matrix, byte[] xlsContent, Properties p) {

		this.matrix = matrix;

		try {
			matrixIn = new FileInputStream(matrix);
			xlsIn = new ByteArrayInputStream(xlsContent);
			matrixPoi = new POIFSFileSystem(matrixIn);
			xlsPoi = new POIFSFileSystem(xlsIn);
			matrixWb = new HSSFWorkbook(matrixPoi);
			xlsWb = new HSSFWorkbook(xlsPoi);
			this.p = p;
			loadMixedTableData();
			/*
			 * p = new Properties(); p.load(this.getClass().getResourceAsStream(
			 * "/matrixsymbols.properties"));
			 */
		} catch (Exception ex) {
			log.error(ex.getMessage(), ex);
		}
	}

	/**
	 * Initializes matrix workbook and xls workbook
	 * 
	 * @param matrix
	 * @param xls
	 */
	public XLSReader(File matrix, byte[] xlsContent, String xlsFileName, Properties p) {
		this.xls = new File(xlsFileName);
		this.matrix = matrix;

		try {
			matrixIn = new FileInputStream(matrix);
			xlsIn = new ByteArrayInputStream(xlsContent);
			matrixPoi = new POIFSFileSystem(matrixIn);
			xlsPoi = new POIFSFileSystem(xlsIn);
			matrixWb = new HSSFWorkbook(matrixPoi);
			xlsWb = new HSSFWorkbook(xlsPoi);
			this.p = p;
			loadMixedTableData();
			/*
			 * p = new Properties(); p.load(this.getClass().getResourceAsStream(
			 * "/matrixsymbols.properties"));
			 */
		} catch (Exception ex) {
			log.error(ex.getMessage(), ex);
		}
	}

	/**
	 * Initializes matrix workbook and xls workbook Please use XLSReader(byte[]
	 * matrixContent, File xls, Properties p)
	 * 
	 * @param matrix
	 * @param xls
	 */
	@Deprecated
	public XLSReader(File matrix, File xls, Properties p) {
		this.matrix = matrix;
		this.xls = xls;
		try {
			matrixIn = new FileInputStream(matrix);
			xlsIn = new FileInputStream(xls);
			matrixPoi = new POIFSFileSystem(matrixIn);
			xlsPoi = new POIFSFileSystem(xlsIn);
			matrixWb = new HSSFWorkbook(matrixPoi);
			xlsWb = new HSSFWorkbook(xlsPoi);
			this.p = p;
			loadMixedTableData();
			/*
			 * p = new Properties(); p.load(this.getClass().getResourceAsStream(
			 * "/matrixsymbols.properties"));
			 */
		} catch (Exception ex) {
			log.error(ex.getMessage(), ex);
		}
	}

	private OptionsRowInfo getOptionsRowInfo(int rowIndex) {
		OptionsRowInfo info = new OptionsRowInfo();
		info.setId((int) Float.parseFloat(getValueByRowCol(matrixWb, "options", rowIndex, 0).toString()));
		info.setCode(getValueByRowCol(matrixWb, "options", rowIndex, 1).toString());
		Object name = getValueByRowCol(matrixWb, "options", rowIndex, 2);
		info.setName((name != null) ? name.toString() : "");
		String type = getValueByRowCol(matrixWb, "options", rowIndex, 3).toString().trim().toLowerCase();
		if (type.equals(TableType.MCT.getMessageCode())) {
			info.setType(TableType.MCT);
		} else if (type.equals(TableType.MIXED.getMessageCode())) {
			info.setType(TableType.MIXED);
		} else if (type.equals(TableType.MULTIVCT.getMessageCode().toLowerCase())) {
			info.setType(TableType.MULTIVCT);
		} else if (type.equals(TableType.MULTIMCT.getMessageCode().toLowerCase())) {
			info.setType(TableType.MULTIMCT);
		} else if (type.equals(TableType.NT.getMessageCode())) {
			info.setType(TableType.NT);
		} else if (type.equals(TableType.VCT.getMessageCode())) {
			info.setType(TableType.VCT);
		}
		Object startCol = getValueByRowCol(matrixWb, "options", rowIndex, 4);
		info.setStartColumnName(startCol != null ? startCol.toString() : null);
		Object startRow = getValueByRowCol(matrixWb, "options", rowIndex, 5);
		if (startRow != null)
			info.setStartRowNumber((int) Float.parseFloat(startRow.toString()));
		else
			info.setStartRowNumber(-1);
		Object endRow = getValueByRowCol(matrixWb, "options", rowIndex, 6);
		if (endRow != null)
			info.setEndRow((int) Float.parseFloat(getValueByRowCol(matrixWb, "options", rowIndex, 6).toString()));
		else
			info.setEndRow(-1);
		info.setProtected(Boolean.getBoolean(getValueByRowCol(matrixWb, "options", rowIndex, 7).toString().trim().toLowerCase()));
		Object eofFirstPrefix = getValueByRowCol(matrixWb, "options", rowIndex, 8);
		info.setEofFirst(eofFirstPrefix + "=" + getValueByRowCol(matrixWb, "options", rowIndex, 9));
		Object eofSecondPrefix = getValueByRowCol(matrixWb, "options", rowIndex, 10);
		info.setEofSecond(eofSecondPrefix + "=" + getValueByRowCol(matrixWb, "options", rowIndex, 11));
		info.setSheetName(getValueByRowCol(matrixWb, "options", rowIndex, 12).toString());
		Object startString = getValueByRowCol(matrixWb, "options", rowIndex, 13);
		info.setMixedTableStartString(startString != null ? startString.toString() : null);
		Object subTypeObject = getValueByRowCol(matrixWb, "options", rowIndex, 15);
		// String subType = subTypeObject != null ?
		// subTypeObject.toString().trim().toLowerCase() : null;
		// if (type.equals(TableType.MCT.getMessageCode())) {
		// info.setSubType(TableType.MCT);
		// } else if (type.equals(TableType.MIXED.getMessageCode())) {
		// info.setSubType(TableType.MIXED);
		// } else if (type.equals(TableType.NT.getMessageCode())) {
		// info.setSubType(TableType.NT);
		// } else if (type.equals(TableType.VCT.getMessageCode())) {
		// info.setSubType(TableType.VCT);
		// }
		Object afterRowsObject = getValueByRowCol(matrixWb, "options", rowIndex, 14);
		if (afterRowsObject != null)
			info.setAfterRows((int) Double.parseDouble(afterRowsObject.toString()));
		return info;
	}

	private void loadMixedTableData() {
		pointMap = new HashMap<String, List<OptionsRowInfo>>();
		Object val = null;
		int rowIndex = 1;
		int colIndex = 3;
		do {
			val = getValueByRowCol(matrixWb, "options", rowIndex, colIndex);
			if (val == null)
				break;
			if (val.toString().trim().toLowerCase().equals(TableType.MIXED.getMessageCode())) {
				// XlsPoint posint = new XlsPoint(rowIndex, colIndex);
				String sheetName = getValueByRowCol(matrixWb, "options", rowIndex, 12).toString();
				if (pointMap.get(sheetName) == null) {
					ArrayList<OptionsRowInfo> rows = new ArrayList<OptionsRowInfo>();
					rows.add(getOptionsRowInfo(rowIndex));
					pointMap.put(sheetName, rows);
				} else {
					while (val.toString().trim().toLowerCase().equals(TableType.MIXED.getMessageCode())) {
						pointMap.get(sheetName).add(getOptionsRowInfo(rowIndex));
						rowIndex++;
						val = getValueByRowCol(matrixWb, "options", rowIndex, colIndex);
					}
				}
				rowIndex++;
			} else {
				rowIndex++;
			}
		} while (val != null);
	}

	public XLSReader(File matrix, Properties p) {
		this.matrix = matrix;
		try {
			matrixIn = new FileInputStream(matrix);
			matrixPoi = new POIFSFileSystem(matrixIn);
			matrixWb = new HSSFWorkbook(matrixPoi);
			this.p = p;
			/*
			 * p = new Properties(); p.load(this.getClass().getResourceAsStream(
			 * "/matrixsymbols.properties"));
			 */
		} catch (Exception ex) {
			log.error(ex.getMessage(), ex);
		}

	}

	public XLSReader(byte[] matrixContent, Properties p) {

		try {
			matrixIn = new ByteArrayInputStream(matrixContent);
			matrixPoi = new POIFSFileSystem(matrixIn);
			matrixWb = new HSSFWorkbook(matrixPoi);
			this.p = p;
			/*
			 * p = new Properties(); p.load(this.getClass().getResourceAsStream(
			 * "/matrixsymbols.properties"));
			 */
		} catch (Exception ex) {
			log.error(ex.getMessage(), ex);
		}

	}

	private XlsPoint getXYReferenceFromMatrix(String sheetName, int rowIndex, int colIndex) {
		// int rowCol[] = new int[2];
		XlsPoint point = new XlsPoint();
		Object value = null;

		HSSFSheet sheet = matrixWb.getSheet(sheetName);
		HSSFRow row = sheet.getRow(rowIndex);
		if (row != null) {
			HSSFCell cell = row.getCell(colIndex);
			if (cell != null) {
				if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK)
					value = cell.getCellComment();
				else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN)
					value = cell.getBooleanCellValue();
				else if (cell.getCellType() == HSSFCell.CELL_TYPE_ERROR)
					value = cell.getErrorCellValue();
				else if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA)
					value = cell.getCellFormula();
				else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
					value = cell.getNumericCellValue();
				else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING)
					value = cell.getRichStringCellValue();
			}
		}

		if (value != null) {
			try {
				String rowString = "";
				String colString = "";
				for (int i = 0; i < value.toString().length(); i++) {
					if (Character.isLetter(value.toString().charAt(i))) {
						continue;
					} else {
						colString = value.toString().substring(0, i);
						rowString = value.toString().substring(i);
						break;
					}
				}

				point.setRowIndex(Integer.parseInt(rowString) - 1);
				point.setColIndex(Integer.parseInt(p.get(colString.toUpperCase()).toString()));
			} catch (Exception ex) {
				log.error(ex.getMessage(), ex);
				ex.printStackTrace();
			}
		}
		return point;
	}

	public boolean containsSheet(HSSFWorkbook wb, String sheetName) {
		boolean contains = false;
		HSSFSheet sheet = wb.getSheet(sheetName);
		if (sheet != null)
			contains = true;
		return contains;
	}

	public Object getValueByRowCol(HSSFWorkbook wb, String sheetName, int rowIndex, int colIndex) {
		Object value = null;
		if (wb == null)
			return value;
		HSSFSheet sheet = wb.getSheet(sheetName);
		HSSFRow row = sheet.getRow(rowIndex);
		if (row != null) {
			HSSFCell cell = row.getCell(colIndex);
			if (cell != null) {
				if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK)
					value = cell.getCellComment();
				else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN)
					value = cell.getBooleanCellValue();
				else if (cell.getCellType() == HSSFCell.CELL_TYPE_ERROR)
					value = cell.getErrorCellValue();
				else if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA)
					value = cell.getCellFormula();
				else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
					value = cell.getNumericCellValue();
					Double d = Double.parseDouble(value.toString());
					if (HSSFDateUtil.isCellDateFormatted(cell)) {
						SimpleDateFormat format = new SimpleDateFormat("dd/MM/yyyy");
						value = format.format(HSSFDateUtil.getJavaDate(d));
					}
				} else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING)
					value = cell.getRichStringCellValue();

			}
		}
		return value;
	}

	private boolean isNumber(String s) {
		boolean isNum = true;
		try {
			Long.parseLong(s);
		} catch (NumberFormatException ex) {
			isNum = false;
		}
		return isNum;
	}

	public List<String> getUnExistingSheets() {
		ArrayList<String> unExistingSheets = new ArrayList<String>();
		Object sheetName = "";
		int i = 1;
		while (sheetName != null) {
			sheetName = getValueByRowCol(matrixWb, "options", i, 12);
			if (sheetName == null)
				break;
			String s = sheetName.toString().trim();
			if (containsSheet(matrixWb, s)) {
				unExistingSheets.add(s);
			}
			i++;
		}
		return unExistingSheets;
	}

	/**
	 * 
	 * @param sheetName
	 * @return the HeaderType object which contains the header information of
	 *         generated xml.
	 */
	public HEADERTYPE getHeaderType(String retVer, String sheetName, String... pattern) {
		log.info("Generating xml header...");
		HEADERTYPE ht = new HEADERTYPE();
		String s = xls.getName();
		if (correctPattern == null)
			for (int i = 0; i < pattern.length; i++) {
				if (s.matches(pattern[i])) {
					correctPattern = pattern[i];
					break;
				}
			}
		if (type == null)
			type = XlsNameParser.getDateType(correctPattern, s);
		String returnCode = null;

		for (int i = 1;; i++) {
			Object o = getValueByRowCol(matrixWb, "Options", i, 12);
			String str = null;
			if (o != null) {
				str = o.toString();
				if (sheetName.equals(str)) {
					returnCode = getValueByRowCol(matrixWb, "Options", i, 1).toString();
					break;
				}
			} else {
				break;
			}
		}

		ht.setRETURNCODE(returnCode);
		ht.setBANKCODE(XlsNameParser.getBankCode(s, pattern));
		String month = XlsNameParser.getMonth(s);// s.substring(9, 11);
		String year = XlsNameParser.getYear(s);// s.substring(11, 15);

		Integer m = new Integer(month);

		if (XlsNameParser.getDateType(correctPattern, s).equalsIgnoreCase("j") || XlsNameParser.getDateType(correctPattern, s).equalsIgnoreCase("d")) {
			int day = XlsNameParser.getDay(s);
			ht.setPERIODEND(XlsNameParser.getPeriodEnd(day, m, type, Integer.parseInt(year)));
			ht.setPERIODFROM(XlsNameParser.getPeriodStart(day, m, type, Integer.parseInt(year)));
		} else {
			ht.setPERIODEND(XlsNameParser.getPeriodEnd(m, type, Integer.parseInt(year)));
			ht.setPERIODFROM(XlsNameParser.getPeriodStart(m, type, Integer.parseInt(year)));
		}
		Object bankName = "Bank Name";// getValueByRowCol(xlsWb, sheetName, 1,
		// 2);
		Object retName = "Return Name";// getValueByRowCol(xlsWb, sheetName, 3,
		// 2);
		ht.setBANKNAME((bankName != null) ? bankName.toString() : " ");
		ht.setRETURNNAME((retName != null) ? retName.toString() : " ");
		ht.setVER(retVer);
		ht.setSIGNED("null");
		ht.setLNG("en_US");
		log.info(ht);
		log.info("XML header generated....");
		return ht;
	}

	private boolean rowEmpty(String sheetName, int colStartIndex, int colEndIndex, int rowIndex) {
		boolean empty = true;
		int emptyCounter = 0;
		for (int i = colStartIndex; i <= colEndIndex; i++) {
			Object val = getValueByRowCol(xlsWb, sheetName, rowIndex, i);
			if (val != null && val.toString().trim().length() != 0) {
				empty = false;
				break;
			}
		}
		return empty;
	}

	private int getVCTStartRow(String sheetName) {
		int vctStartRow = -1;
		int colNum = 12;// Column M index is 12

		for (int rowNum = 1;; rowNum++) {
			Object val = getValueByRowCol(matrixWb, "options", rowNum, colNum);
			if ((val == null) || (val.toString().trim().equals("")))
				break;
			if (sheetName.toLowerCase().equals(val.toString().trim().toLowerCase())) {
				Object vctString = getValueByRowCol(matrixWb, "options", rowNum, 3);
				if ((vctString != null) && (vctString.toString().toLowerCase().trim().equals("vct"))) {
					Object vctStartRowVal = getValueByRowCol(matrixWb, "options", rowNum, 5);
					vctStartRow = (int) Float.parseFloat(vctStartRowVal.toString());
				}
			}
		}

		return vctStartRow;
	}

	public int getEofIndexRow(HSSFWorkbook matrixWb, String sheetName, int vctStartRow) {
		HSSFSheet currSheet = xlsWb.getSheet(sheetName);
		log.info("Getting Filled Rows For Sheet " + sheetName, sheetName);
		int rowCount = currSheet.getLastRowNum();
		log.info("Filled Row Number : " + rowCount);
		int index = vctStartRow;
		Object code = null;
		int r = 1;

		while ((code = getValueByRowCol(matrixWb, "options", r, 1)) != null) {
			if (code.toString().toLowerCase().trim().contains(sheetName.toLowerCase().trim()))
				break;
			else
				r++;
		}

		Object eofOneColName = getValueByRowCol(matrixWb, "options", r, 8).toString();
		Object eofOneValue = getValueByRowCol(matrixWb, "options", r, 9);
		Object eofTwoColName = getValueByRowCol(matrixWb, "options", r, 10).toString();
		Object eofTwoValue = getValueByRowCol(matrixWb, "options", r, 11);
		int eofOneColIndex = Integer.parseInt(p.get(eofOneColName).toString());
		int eofTwoColIndex = Integer.parseInt(p.get(eofTwoColName).toString());

		log.info("Trying to find stop condition in XLS for sheet " + sheetName);
		for (int i = vctStartRow; i < rowCount; i++) {
			Object s1 = getValueByRowCol(xlsWb, sheetName, i, eofOneColIndex);
			Object s2 = getValueByRowCol(xlsWb, sheetName, i, eofTwoColIndex);
			if (s1 == null)
				s1 = "";
			if (s2 == null)
				s2 = "";
			if (eofOneValue == null)
				eofOneValue = "";
			if (eofTwoValue == null)
				eofTwoValue = "";
			// if ((s1 == null || s1.toString().trim().equals("")) && (s2 ==
			// null || s2.toString().trim().equals(""))) {
			// index = i;
			// log.info("Stop condition found at row index " + index);
			// break;
			// } else
			if ((s1.toString().trim().toLowerCase().equals(eofOneValue.toString().trim().toLowerCase())) && (s2.toString().trim().toLowerCase().equals(eofTwoValue.toString().trim().toLowerCase()))) {
				index = i;
				break;
			}

		}

		if (index == vctStartRow) {
			index = rowCount;
		}

		log.info("Stop condition found at row index " + index);
		return index;
	}

	private ArrayList<ITEMTYPE> loadVCTItems(String sheetName, int vctStartRow, int startRowIndexInMatrixSheet) {
		ArrayList<ITEMTYPE> data = new ArrayList<ITEMTYPE>();
		log.info("Getting End Of File Index Row");
		int eofIndexRow = getEofIndexRow(matrixWb, sheetName, vctStartRow);
		log.info("Got End Of File Index Row", eofIndexRow);
		for (int j = startRowIndexInMatrixSheet;; j++) {
			Object code = getValueByRowCol(matrixWb, sheetName, j, 0);
			if ((code == null) || (code.toString().trim().length() == 0))
				break;
			Object colName = getValueByRowCol(matrixWb, sheetName, j, 2);
			if ((colName == null) || (colName.toString().trim().length() == 0))
				break;

			Integer colIndex = Integer.parseInt(p.get(colName.toString()).toString());

			for (int i = vctStartRow, k = 0; i <= eofIndexRow; i++, k++) {
				ITEMTYPE item = new ITEMTYPE();
				item.setITEMCODE(code.toString().trim());
				item.setROW(k);
				Object val = getValueByRowCol(xlsWb, sheetName, i - 1, colIndex);
				item.setVALUE((val != null) ? val.toString() : "");
				data.add(item);
			}

		}
		return data;
	}

	private ArrayList<ITEMTYPE> loadMultiVCTItems(String sheetName, int rowIndex, OptionsRowInfo info) {
		ArrayList<ITEMTYPE> data = new ArrayList<ITEMTYPE>();

		log.info("Generating Multi VCT Items for Sheet " + sheetName);
		Object newSheetNameObj = getValueByRowCol(matrixWb, "options", rowIndex, 12);
		String newSheetName = newSheetNameObj != null ? newSheetNameObj.toString() : null;
		int currVCTCounter = 0;
		if (newSheetName != null) {
			while (newSheetName.trim().toLowerCase().equals(sheetName.toLowerCase().trim())) {
				log.info("NewSheetName " + newSheetName + " rowIndex= " + rowIndex);
				log.info(info);

				{
					String startColName = info.getStartColumnName();
					String label = info.getMixedTableStartString();
					log.info("Label " + label + " Col Name " + startColName);

					XlsPoint startTablePoint = getLabelPoint(startColName, label, sheetName);
					int afterRows = info.getAfterRows();
					int startRowIndex = startTablePoint.getRowIndex() + afterRows;
					log.info("##############" + getValueByRowCol(xlsWb, sheetName, startRowIndex, startTablePoint.getColIndex()));
					int startRowIndexInMatrixSheet = info.getEndRow() <= 0 ? 1 : info.getEndRow() - 1;
					data.addAll(loadVCTItems(sheetName, startRowIndex + 1, startRowIndexInMatrixSheet));
					// loadVCTItems(newSheetName, vctStartRow, 1);
					// rowIndex = getRowIndexBySheet(sheetName);

					currVCTCounter++;
				}
				rowIndex++;
				newSheetNameObj = getValueByRowCol(matrixWb, "options", rowIndex, 12);
				newSheetName = newSheetNameObj != null ? newSheetNameObj.toString() : null;

				if (newSheetName == null) {
					break;
				} else {
					info = getOptionsRowInfo(rowIndex);
				}
			}
			log.info("Generated VCT Items");
		}
		return data;
	}

	private ArrayList<ITEMTYPE> loadMCTItems(String sheetName, int rowIndex) {
		ArrayList<ITEMTYPE> data = new ArrayList<ITEMTYPE>();
		for (int i = rowIndex;; i++) {
			ITEMTYPE item = new ITEMTYPE();
			Object code = getValueByRowCol(matrixWb, sheetName, i, 0);
			if ((code == null) || (code.toString().trim().length() == 0))
				break;
			else {
				item.setITEMCODE(code.toString().trim());
				item.setROW(0);
				XlsPoint point = getXYReferenceFromMatrix(sheetName, i, 2);
				Object val = getValueByRowCol(xlsWb, sheetName, point.getRowIndex(), point.getColIndex());
				if (val != null)
					item.setVALUE(val.toString());
				else
					item.setVALUE("");
				data.add(item);
			}
		}
		return data;
	}

	/**
	 * 
	 * @param sheetName
	 * @return the List of ItemsInfoType,each ItemsInfoType object containts
	 *         code,description and ammount
	 */
	public List<ITEMTYPE> getAllItems(String sheetName) {
		log.info("Generating xml items for sheet " + sheetName);

		ArrayList<ITEMTYPE> data = new ArrayList<ITEMTYPE>();
		// ////////////////////////////////////////////
		if ((pointMap != null) && (pointMap.containsKey(sheetName))) {
			List<OptionsRowInfo> list = pointMap.get(sheetName);
			for (OptionsRowInfo inf : list) {
				int vctStartRow = inf.getStartRowNumber();
				if (vctStartRow != -1) {
					// TODO
					ArrayList<ITEMTYPE> tmp = loadVCTItems(sheetName, vctStartRow, 1);
					for (ITEMTYPE it : tmp) {
						data.add(it);
					}
				} else {
					// TODO
					ArrayList<ITEMTYPE> tmp = loadMCTItems(sheetName, inf.getEndRow());
					for (ITEMTYPE it : tmp) {
						data.add(it);
					}
				}
			}
			pointMap.remove(sheetName);
		} else {
			int rowIndex = getRowIndexBySheet(sheetName);
			OptionsRowInfo info = getOptionsRowInfo(rowIndex);
			if (info.getType() == TableType.MULTIVCT) {
				data = loadMultiVCTItems(sheetName, rowIndex, info);
			} else {
				int vctStartRow = getVCTStartRow(sheetName);
				if (vctStartRow == -1) {
					data = loadMCTItems(sheetName, 1);
				} else {
					data = loadVCTItems(sheetName, vctStartRow, 1);
				}
			}
		}
		log.info("XML items generated...");
		return data;
	}

	/**
	 * 
	 * @param startColName
	 * @param label
	 * @return XlsPoint object which contains row index and col index of Table
	 *         to be read
	 */
	private XlsPoint getLabelPoint(String startColName, String label, String xlsSheetName) {
		XlsPoint point = new XlsPoint();
		int colIndex = Integer.parseInt(p.get(startColName.toUpperCase().trim()).toString());
		point.setColIndex(colIndex);
		point.setRowIndex(-1);
		HSSFSheet sheet = xlsWb.getSheet(xlsSheetName);
		int lastRowNum = sheet.getLastRowNum();
		int rowIndex = -1;
		do {
			rowIndex++;
			Object val = getValueByRowCol(xlsWb, xlsSheetName, rowIndex, colIndex);
			if (val != null && val.toString().trim().toLowerCase().equals(label.toLowerCase().trim())) {
				point.setRowIndex(rowIndex);
				break;
			}
		} while (rowIndex != lastRowNum);
		return point;
	}

	/**
	 * 
	 * @return sheets first row index from Matrix.xx.xls options sheet
	 */
	private int getRowIndexBySheet(String sheetName) {
		int rowIndex = 1;
		int colIndex = 12;
		boolean equals = false;
		HSSFSheet currSheet = matrixWb.getSheet("options");
		int rowCount = currSheet.getLastRowNum();
		for (int i = rowIndex; i < rowCount; i++) {
			Object val = getValueByRowCol(matrixWb, "options", rowIndex, colIndex);
			if (val.toString().trim().equals(sheetName.trim())) {
				equals = true;
				break;
			} else {
				rowIndex++;
			}
		}
		return rowIndex;
	}

	/**
	 * 
	 * @return the array of matrix sheets names
	 */
	public String[] getAllSheets() {
		String sheets[] = new String[matrixWb.getNumberOfSheets()];
		for (int i = 0; i < sheets.length; i++)
			sheets[i] = matrixWb.getSheetName(i);
		return sheets;
	}

	public TreeMap<String, Boolean> loadProtectionProperties() {
		TreeMap<String, Boolean> optionsMap = new TreeMap<String, Boolean>();
		Object retName = null;
		if (matrixWb == null)
			return optionsMap;
		int j = 1;
		try {
			while ((retName = getValueByRowCol(matrixWb, "options", j, 12)) != null) {
				String key = retName.toString().trim();
				String val = getValueByRowCol(matrixWb, "options", j, 7).toString().trim().toLowerCase();
				Boolean b;
				if (val.equals("true"))
					b = true;
				else
					b = false;
				optionsMap.put(key, b);
				j++;
			}
		} catch (Exception ex) {
			log.error(ex.getMessage(), ex);
		}
		return optionsMap;
	}

	public File getXls() {
		return xls;
	}

	public XlsPoint searchBy(String value, int startRow, int colIndex) {
		XlsPoint point = null;
		Object val = null;
		do {
			val = getValueByRowCol(matrixWb, "Options", startRow, colIndex);
			if ((val == null) || (val.toString().trim().equals(""))) {
				break;
			}
			if (val.toString().trim().toLowerCase().equals(value.toLowerCase().trim())) {
				point = new XlsPoint();
				point.setRowIndex(startRow);
				point.setColIndex(colIndex);
			}
			startRow++;
		} while (true);
		return point;
	}

	public File getMatrix() {
		return matrix;
	}

	public Map<String, Short> getAllSheetData() {
		Map<String, Short> m = new HashMap<String, Short>();
		String sheets[] = this.getAllSheets();
		for (String s : sheets) {
			m.put(s, matrixWb.getSheet(s).getPassword());
		}
		return m;
	}

	public Map<String, Boolean> getAllTrueFalseData() {
		Map<String, Boolean> m = new HashMap<String, Boolean>();
		int rowIndex = 1;
		while (getValueByRowCol(matrixWb, "Options", rowIndex, 1) != null) {
			Object key = getValueByRowCol(matrixWb, "Options", rowIndex, 12);
			Object val = getValueByRowCol(matrixWb, "Options", rowIndex, 7);
			if (key != null && key.toString().trim().length() != 0 && val != null && val.toString().trim().length() != 0) {
				m.put(key.toString(), Boolean.parseBoolean(val.toString()));
			}
			rowIndex++;
		}
		return m;
	}
}